﻿use master
create database company 
go
use company 
go
create table sectionInfo--部门信息表
(
sectionID  int primary key identity(1,1),--部门编号
sectionName  varchar(10)  not null,--部门名称
)
insert into sectionInfo(sectionName)
values('人力资源部'),('财务部'),('市场营销部'),
      ('技术研发部'),('后勤管理部')


go
create table userInfo--员工信息表
(
userNo  int  primary key identity(1,1) not null,--员工编号
userName  varchar(10) unique not null,--员工姓名
userSex   varchar(2) not null check(userSex='男' or userSex='女'),--员工性别
userAge   int not null check( userAge>=1 and  userAge<=100),--员工年龄
userCity  varchar(50) default('福建省龙岩市'),--所在城市
userSection  int references sectionInfo(sectionID),--员工部门  
userSalary decimal(5,2) default(0)--员工月薪
)

insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
values('刘正','男','20','福建省龙岩市',1,100),
      ('黄贵','男','21','福建省龙岩市',2,101),
      ('陈美','女','22','福建省龙岩市',3,102),
      ('江文','男','23','福建省龙岩市',4,103),
      ('钟琪','女','24','福建省龙岩市',5,104),
      ('曾小林','男','25','福建省龙岩市',1,105),
      ('欧阳天天','女','26','福建省龙岩市',2,106),
      ('李逍遥','男','27','福建省龙岩市',3,107),
      ('刘德华','男','28','福建省龙岩市',4,108),
      ('刘翔','男','29','福建省龙岩市',5,109),
      ('曾小贤','男','30','福建省龙岩市',1,110),
      ('刘','男','31','福建省龙岩市',2,111),
      ('陈天翔','男','32','福建省龙岩市',3,112),
      ('刘能','男','33','福建省龙岩市',4,113),
      ('钟馗','男','34','福建省龙岩市',5,114),
      ('钟吴艳','女','35','福建省龙岩市',1,115),
      ('刘欢','男','36','福建省龙岩市',2,116),
      ('张庭','女','37','福建省龙岩市',3,117),
      ('曹植','男','38','福建省龙岩市',4,118),
      ('曹操','男','39','福建省龙岩市',5,119)
      


go
create table workInfo--员工考勤表
(
workId int primary key identity(1,1) not null,--考勤编号
userId  int references userInfo(userNo) not null,--考勤员工
workTime datetime not null,--考勤时间
workDescription  varchar(40)not null check(workDescription='迟到' or workDescription='早退' or
                                           workDescription='旷工' or workDescription='病假' or
										   workDescription='事假' or workDescription='出勤' )
)

insert into workInfo(userId,workTime,workDescription)
values(1,2002-08-02,'迟到'),
      (2,2003-07-02,'早退'),
      (3,2002-07-22,'旷工'),
      (4,2001-07-02,'病假'),
      (5,2003-01-13, '事假'),
	  (6,2003-05-15,'出勤'),
      (7,2002-08-19,'迟到'),
      (8,2003-09-02,'早退'),
      (9,2003-06-11,'旷工'),
      (10,2003-07-09,'病假'),
      (11,2003-07-02,'事假'),
      (12,2002-07-02,'出勤'),
      (13,2002-07-02,'迟到'),
      (14,2002-08-02,'早退'),
      (15,2002-08-02,'旷工'),
      (16,2002-08-02,'病假'),
      (17,2002-07-02,'事假'),
      (18,2002-07-02,'出勤'),
      (19,2002-08-02,'迟到'),
      (20,2002-08-02,'早退')
     

--1.查询公司的部门数量
select count(*)部门数量 from sectionInfo
--2.查询公司的员工数量
select count(*)员工数量 from userInfo
--3.查询所有部门的员工数量和工资平均值
select count(userSection) 各部门员工数量,avg(userSalary) 该部门平均工资 
from userInfo 
group by userSection

--4.查询每个年龄的男女生人数
select count(userSex) 人数,userSex 性别, userAge 年龄
 from userInfo 
 group by userAge,userSex

--5.查询所有部门员工的平均薪资

select (case when userSection=1 then '人力资源部'
			 when userSection=2 then '财务部'
			 when userSection=3 then '市场营销部'
			 when userSection=4 then '技术研发部'
			 when userSection=5 then '后勤管理部'
		end ) 部门名称,avg(userSalary) 该部门平均工资 
		from userInfo 
		group by userSection
--6.查询员工最高工资和最低工资的差距
 select max(userSalary) 最高工资,min(userSalary) 最低工资,
 max(userSalary)-min(userSalary) 差距 
 from userInfo

--7.查询平均工资高于 8000 的部门 id 和它的平均工资.
   select userSection 部门编号,avg(userSalary)平均工资 
   from userInfo 
   group by userSection 
   having avg(userSalary) > 8000

--8.查询公司员工工资的最大值，最小值，平均值，总和
 select  max(userSalary) 最高工资,min(userSalary) 最低工资,
 avg(userSalary) 平均工资,sum(userSalary) 总和  
 from userInfo

--9. (有员工的城市)各个城市的平均工资
select userCity 城市名称,avg(userSalary) 平均工资 
from userInfo 
group by userCity

--10.查询每个员工本月的出勤情况信息
select workInfo.userId 员工编号,
(case when workDescription='出勤' then 1 else 0 end) 出勤,
(case when workDescription='迟到' then 1 else 0 end) 迟到,
(case when workDescription='事假' then 1 else 0 end) 事假,
(case when workDescription='病假' then 1 else 0 end) 病假,
(case when workDescription='早退' then 1 else 0 end) 早退,
(case when workDescription='旷工' then 1 else 0 end) 旷工
 from workInfo